# -*- coding: UTF-8 -*-
from pymysql import NULL
from utils.excel_util import ExcelHelper
import datetime
from config.logging_config import logger
def read_excel():
    # 读取文件
    fileName = "C:\\Users\\14984\\Desktop\\test.xlsx"
    sheetName = "Sheet1"
    excel_helper = ExcelHelper(fileName, sheetName)
    data_list = excel_helper.get_data_list()
    logger.info(data_list)


def query_database(mysqlHelper, temp_sql):
    # 查库
    result = mysqlHelper.get_list(temp_sql)
    return result


# 处理数据
def deal_data(item, column_name, column_type_dict):
    data = item.get(column_name)
    # 是否必填
    column_is_nullable = column_type_dict.get(column_name + "_is_nullable").lower()
    # 字段类型
    column_data_type = column_type_dict.get(column_name + "_data_type").lower()
    # 时间
    timestamp = datetime.datetime.now()
    formatted_date = timestamp.strftime("%Y-%m-%d")
    if data is None or data == '':
        if column_is_nullable == "no" and "datetime" == column_data_type:
            return "'" + f'{formatted_date}' + " 00:00:00'"
        if column_is_nullable == "no" and "date" == column_data_type:
            return "'" + f'{formatted_date}' + "'"
        if column_is_nullable == "no":
            return "'此项数据为空'"
        else:
            return NULL
    # 按类型处理
    if "varchar" == column_data_type or "text" == column_data_type or "datetime" == column_data_type or "date" == column_data_type:
        return "'" + f'{data}' + "'"
    return data


def generate_statement(mysqlHelper, table_schema, table_name, column_filter_list, type):
    if type == 2:
        return mysqlHelper.generate_batch_insert_statement(table_schema=table_schema, table_name=table_name,
                                                           column_filter_list=column_filter_list)
    if type == 1:
        return mysqlHelper.generate_insert_statement(table_schema=table_schema, table_name=table_name,
                                                     column_filter_list=column_filter_list)


def print_params_template_statement(mysqlHelper, table_schema, table_name, column_filter_list):
    mysqlHelper.generate_params_template_statement(table_schema=table_schema,
                                                   table_name=table_name,
                                                   column_filter_list=column_filter_list)


def gen_column_type(mysqlHelper, table_schema, table_name):
    column_type_dict = mysqlHelper.query_column_type(table_schema=table_schema, table_name=table_name)
    return column_type_dict


def gen_single_data(mysqlHelper, table_schema, table_name, column_type_dict, sql_template, replace_template, item,
                    is_insert):
    temp_sql = eval(replace_template)

    if is_insert is True:
        num = mysqlHelper.save(temp_sql)
        return num
    else:
        logger.info("sql生成：\n"+temp_sql)
        return 0


def gen_batch_data(mysqlHelper, table_schema, table_name, column_type_dict, sql_template, values_template,
                   replace_template, data_list, is_insert):
    if data_list is None or len(data_list) == 0:
        return
    values_list = []
    for item in data_list:
        values_list.append(eval(replace_template))

    values_sql = ', '.join(values_list)
    temp_sql = sql_template + values_sql + ";"
    if is_insert is True:
        num = mysqlHelper.save(temp_sql)
        return num
    else:
        logger.info("sql生成：\n"+temp_sql)
        return 0





def start_single_generate_data(data_list, mysqlHelper, table_schema, table_name, column_filter_list, is_insert):
    if data_list is None or len(data_list) == 0:
        return
    # 生成语句
    sql_template, replace_template = generate_statement(mysqlHelper, table_schema, table_name, column_filter_list, 1)
    # 打印参数模板
    if is_insert is False:
        print_params_template_statement(mysqlHelper, table_schema, table_name, column_filter_list)

    # 获取类型
    column_type_dict = gen_column_type(mysqlHelper, table_schema, table_name)

    success = 0
    for data_item in data_list:
        num = gen_single_data(mysqlHelper=mysqlHelper, table_schema=table_schema, table_name=table_name,
                              column_type_dict=column_type_dict, sql_template=sql_template,
                              replace_template=replace_template,
                              item=data_item, is_insert=is_insert)
        success = success + num
    if is_insert is True:
        logger.info("执行结束----")
    else:
        logger.info("生成结束----")
    logger.info("总记录数：" + str(len(data_list)) + ",成功记录数：" + str(success) + ",失败记录数：" + str(
        len(data_list) - success))


def start_batch_generate_data(data_list, mysqlHelper, table_schema, table_name, column_filter_list, is_insert):
    if data_list is None or len(data_list) == 0:
        return
    # 生成语句
    sql_template, values_template, replace_template = generate_statement(mysqlHelper, table_schema, table_name,
                                                                       column_filter_list, 2)
    # 打印参数模板
    if is_insert is False:
        print_params_template_statement(mysqlHelper, table_schema, table_name, column_filter_list)
    # 获取类型
    column_type_dict = gen_column_type(mysqlHelper, table_schema, table_name)
    # 批量执行
    if len(data_list) > 100:
        n = 100  # 分割长度
        spilt_data_list = [data_list[i:i + n] for i in range(0, len(data_list), n)]
        for item_list in spilt_data_list:
            gen_batch_data(mysqlHelper=mysqlHelper, table_schema=table_schema, table_name=table_name,
                           column_type_dict=column_type_dict, sql_template=sql_template,
                           values_template=values_template,
                           replace_template=replace_template,
                           data_list=item_list, is_insert=is_insert)

    else:
        gen_batch_data(mysqlHelper=mysqlHelper, table_schema=table_schema, table_name=table_name,
                   column_type_dict=column_type_dict, sql_template=sql_template, values_template=values_template,
                   replace_template=replace_template,
                   data_list=data_list, is_insert=is_insert)

    if is_insert is True:
        logger.info("执行结束----")
    else:
        logger.info("生成结束----")
    logger.info("总记录数：" + str(len(data_list)))



